<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  

  
  <title>045.数据库索引知识梳理 | 似南-全栈开发路上的探索者、追梦人</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  
  
  
  <meta name="keywords" content="似南,FaithSinan" />
  
  <meta name="description" content="一、什么是索引我们都知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，在数据库中的默认查找算法是最简单，最直接的顺序查找算法。而顺序查找算法在大数据量的情况下，需要查找的内容越靠后所花费的时间就越久。 为了解决这个问题，数据库设计引入了索引这一概念，索引就是为了提高大数据量下数据的查找效率而生的。">
<meta property="og:type" content="article">
<meta property="og:title" content="045.数据库索引知识梳理">
<meta property="og:url" content="http://yoursite.com/2021/11/15/045.%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B4%A2%E5%BC%95%E7%9F%A5%E8%AF%86%E6%A2%B3%E7%90%86/index.html">
<meta property="og:site_name" content="似南-全栈开发路上的探索者、追梦人">
<meta property="og:description" content="一、什么是索引我们都知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，在数据库中的默认查找算法是最简单，最直接的顺序查找算法。而顺序查找算法在大数据量的情况下，需要查找的内容越靠后所花费的时间就越久。 为了解决这个问题，数据库设计引入了索引这一概念，索引就是为了提高大数据量下数据的查找效率而生的。">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="http://yoursite.com/image/045/2-1.png">
<meta property="og:image" content="http://yoursite.com/image/045/2-2.png">
<meta property="og:image" content="http://yoursite.com/image/045/2-3.png">
<meta property="og:image" content="http://yoursite.com/image/045/2-4-1.png">
<meta property="og:image" content="http://yoursite.com/image/045/2-4-2.png">
<meta property="article:published_time" content="2021-11-15T08:06:21.000Z">
<meta property="article:modified_time" content="2021-11-15T08:15:09.976Z">
<meta property="article:author" content="FaithSinan">
<meta property="article:tag" content="索引">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="http://yoursite.com/image/045/2-1.png">
  
    <link rel="alternate" href="/atom.xml" title="似南-全栈开发路上的探索者、追梦人" type="application/atom+xml">
  
  
    <link rel="icon" href="/images/avatar.png">
  
  
    <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  
  
<link rel="stylesheet" href="/css/style.css">

  
<link rel="stylesheet" href="/css/highlight.css">

<meta name="generator" content="Hexo 4.2.1"></head>

<body>
  <div id="fullpage" class="mobile-nav-right">
    
      <div id="wrapper" title="图片来自网络">
    
    
      <header id="header">
  <div id="nav-toggle" class="nav-toggle"></div>
  <div class="head-box global-width">
    <nav class="nav-box nav-left">
      
        <a class="nav-item" href="/" title
        
        >首页</a>
      
        <a class="nav-item" href="/archives" title
        
        >归档</a>
      
    </nav>
  </div>
</header>
      <div id="middlecontent" title class="global-width sidebar-left">
        <section id="main"><article id="post-045.数据库索引知识梳理" class="article global-container article-type-post" itemscope itemprop="blogPost">
  
    <header class="article-header">
      
  
    <h1 class="article-title" itemprop="name">
      045.数据库索引知识梳理
    </h1>
  

    </header>
  
  <div class="article-meta">
    <a href="/2021/11/15/045.%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B4%A2%E5%BC%95%E7%9F%A5%E8%AF%86%E6%A2%B3%E7%90%86/" class="article-date">
  <time datetime="2021-11-15T08:06:21.000Z" itemprop="datePublished">2021-11-15</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a>►<a class="article-category-link" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/MySQL/">MySQL</a>
  </div>

    
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/%E7%B4%A2%E5%BC%95/" rel="tag">索引</a></li></ul>

  </div>
  
    <span id="busuanzi_container_page_pv">
      本文总阅读量<span id="busuanzi_value_page_pv"></span>次
    </span>
  

  <div class="article-inner">
    
    <div class="article-content article-content-cloud" itemprop="articleBody">
      
        <h3 id="一、什么是索引"><a href="#一、什么是索引" class="headerlink" title="一、什么是索引"></a>一、什么是索引</h3><p>我们都知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，在数据库中的默认查找算法是最简单，最直接的顺序查找算法。而顺序查找算法在大数据量的情况下，需要查找的内容越靠后所花费的时间就越久。</p>
<p>为了解决这个问题，数据库设计引入了索引这一概念，索引就是为了提高大数据量下数据的查找效率而生的。</p>
<a id="more"></a>

<h3 id="二、索引的数据结构"><a href="#二、索引的数据结构" class="headerlink" title="二、索引的数据结构"></a>二、索引的数据结构</h3><p>MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。</p>
<p>索引是一种排好序的数据结构。如果把数据库看作是一本书，索引就类似于这本书的目录。通过索引数据库引擎就能快速的定位到对应的内容。</p>
<p>索引有多种数据结构，常见的数据结构有：二叉查找树、红黑树、Hash表、B 树。</p>
<h4 id="1、二叉查找树"><a href="#1、二叉查找树" class="headerlink" title="1、二叉查找树"></a>1、二叉查找树</h4><p>​        指的是一种基于二分查找概念的二叉树，数据量大时相对于顺序查找能较大提升数据查询效率</p>
<p><img src="/image/045/2-1.png" alt="二叉树示意图"></p>
<h4 id="2、红黑树"><a href="#2、红黑树" class="headerlink" title="2、红黑树"></a>2、红黑树</h4><p>​        又称为自平衡二叉查找树，是二叉查找树的优化版本。它左右树高度相差不会高于1，当高于1时会自动进行平衡，让树不至于太失衡。</p>
<p><img src="/image/045/2-2.png" alt="红黑树示意图"></p>
<h4 id="3、Hash表"><a href="#3、Hash表" class="headerlink" title="3、Hash表"></a>3、Hash表</h4><p>​        哈希表结构只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，同时对应的在哈希表中保存指向每个数据行的指针</p>
<p><img src="/image/045/2-3.png" alt="HASH表结构"></p>
<h4 id="4、B-树"><a href="#4、B-树" class="headerlink" title="4、B 树"></a>4、B 树</h4><p>​        一般二叉树每个节点上只存储一个元素，而 B 树每个节点上可以存储多个数值元素。B树与红黑树相似，会自平衡树。与红黑树不同的是，B树是多叉树，且所有的叶子节点都处于同一层级。不存在层级高度差的问题，所以又称为平衡多路查找树。</p>
<p><strong>B 树</strong>可以看作是红黑树的一种扩展，即他允许每个节点有M-1个子节点。</p>
<ul>
<li>根节点至少有两个子节点</li>
<li>每个节点有M-1个key，并且以升序排列</li>
<li>位于M-1和M key的子节点的值位于M-1 和M key对应的Value之间</li>
<li>其它节点至少有M/2个子节点</li>
</ul>
<p>示意图：</p>
<p><img src="/image/045/2-4-1.png" alt="B 树示意图"></p>
<p><strong>B+</strong>树是对B树的一种变形树，它与B树的差异在于：</p>
<ul>
<li>有k个子结点的结点必然有k个关键码</li>
<li>非叶结点仅具有索引作用，跟记录有关的信息均存放在叶结点中</li>
<li>树的所有叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录</li>
</ul>
<p>示意图：</p>
<p><img src="/image/045/2-4-2.png" alt="B+ 树示意图"></p>
<h4 id="5、小结"><a href="#5、小结" class="headerlink" title="5、小结"></a>5、小结</h4><p>MySql 中索引的数据结构通常默认用的是 基于 B树 的 B+树。</p>
<p>因为采用二叉查找树和红黑树遇到大数据量时，树的高度会很高，查找起来效率依然不高。而 Hash 表虽然数据指定查找（=）效率比 B+ 树高，但在范围查找（&gt;&lt;）方面 Hash 表支持不行。B+ 树虽然指定查找效率比 Hash 表差一点，但范围查找支持很好。所以采用 B+ 树更优。</p>
<h3 id="三、InnoDB-索引的特性"><a href="#三、InnoDB-索引的特性" class="headerlink" title="三、InnoDB 索引的特性"></a>三、InnoDB 索引的特性</h3><p>InnoDB 表数据文件本身是按 B+ 树组织的一个索引结构文件。</p>
<p> MyISAM是非聚集索引，索引文件和数据文件是分离存储的。而 InnoDB 的索引是聚集索引，索引文件和数据都在一个B+ 树结构文件中， 聚集索引-叶节点包含了完整的数据记录。</p>
<p> InnoDB 表必须有主键，并且推荐使用整型的自增组件。因为 InnoDB 数据文件是采用 B+ 树组织的，如果没有主键，数据无法组织与维护的。而 索引查找中涉及了大量的比较操作，使用整型便于比较操作，也更节省空间。如不使用自增遇到数据插入 B+ 树前面节点整棵树需要重新计算树结构，分裂节点自动平衡会造成很大的性能开销。</p>
<p>为什么非主键索引结构叶子节点存储的是主键值？为了数据一致性和节省空间。</p>
<p><strong>1. 保持一致性：</strong><br> 当数据库表进行DML操作时，同一行记录的页地址会发生改变，因非主键索引保存的是主键的值，无需进行更改。</p>
<p><strong>2. 节省存储空间：</strong><br> Innodb数据本身就已经汇聚到主键索引所在的B+树上了， 如果普通索引还继续再保存一份数据，就会导致有多少索引就要存多少份数据。</p>
<h3 id="四、索引的类型"><a href="#四、索引的类型" class="headerlink" title="四、索引的类型"></a>四、索引的类型</h3><p>在数据库中，<strong>主键</strong>和<strong>唯一标识</strong>都同时具有约束和索引的特性，给某个字段设置了主键约束或唯一约束就同时设置了索引。通过设置主键约束、唯一约束创建索引，称为间接创建索引。</p>
<ul>
<li>索引的类型有：主键索引、唯一索引、普通索引、全文索引。</li>
<li>索引可以在表创建时增加，也可以在表创建完后增加，可以用 alter 语法或者 create 语法</li>
<li>同一个字段可以设置多种索引</li>
</ul>
<h4 id="1、主键索引（primary-key）"><a href="#1、主键索引（primary-key）" class="headerlink" title="1、主键索引（primary key）"></a>1、主键索引（primary key）</h4><p>主键索引和唯一索引非常类似。<br>事实上，主键索引仅是一个具有名称 primary 的 unique 索引。这表示一个表只能包含一个 primary key。</p>
<p>主键具有唯一性，在一个表中只能有一个字段作为主键，且值不能重复。</p>
<p>表创建时设置:</p>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="SQL"><figure class="iseeu highlight /sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 不指定主键名语法：PRIMARY KEY (字段名)</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> a (</span><br><span class="line">	<span class="string">`id`</span> <span class="built_in">INT</span> ,</span><br><span class="line">	PRIMARY <span class="keyword">KEY</span> (<span class="string">`id`</span>)</span><br><span class="line">)<span class="keyword">ENGINE</span>=<span class="keyword">INNODB</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=UTF8;</span><br></pre></td></tr></table></figure></div>



<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="SQL"><figure class="iseeu highlight /sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 指定主键名语法：CONSTRAINT 主键名 PRIMARY KEY (字段名)</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> a (</span><br><span class="line">	<span class="string">`id`</span> <span class="built_in">INT</span> ,</span><br><span class="line">	<span class="keyword">CONSTRAINT</span> <span class="string">`pk_id`</span> PRIMARY <span class="keyword">KEY</span> (<span class="string">`id`</span>)</span><br><span class="line">)<span class="keyword">ENGINE</span>=<span class="keyword">INNODB</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=UTF8;</span><br></pre></td></tr></table></figure></div>



<p>表创建后增加: </p>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="SQL"><figure class="iseeu highlight /sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 语法：ALTER TABLE 表名 ADD PRIMARY KEY 字段名 (主键约束名)</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="string">`a`</span> <span class="keyword">ADD</span> PRIMARY <span class="keyword">KEY</span> <span class="string">`id`</span> (<span class="string">`pk_id`</span>)</span><br></pre></td></tr></table></figure></div>



<h4 id="2、唯一索引（unique-key）"><a href="#2、唯一索引（unique-key）" class="headerlink" title="2、唯一索引（unique key）"></a>2、唯一索引（unique key）</h4><p>唯一索引字段值唯一，唯一索引可以设置多个但值不允许重复，在一个表中可以有多个唯一字段。唯一索引和主键索引的设置方法类似。</p>
<p>表创建时设置：</p>
<ul>
<li>语法：UNIQUE KEY (字段名)</li>
</ul>
<p>表创建后增加：</p>
<ul>
<li>语法：ALTER TABLE 表名 ADD UNIQUE KEY 字段名 (唯一约束名)</li>
</ul>
<h4 id="3、普通索引（key-index）"><a href="#3、普通索引（key-index）" class="headerlink" title="3、普通索引（key / index）"></a>3、普通索引（key / index）</h4><p>默认的索引，可用 key 或 index 来设置。</p>
<p>表创建时设置：</p>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="SQL"><figure class="iseeu highlight /sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 语法：INDEX 索引名 (字段名) USING 索引数据结构</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> a(</span><br><span class="line">	<span class="string">`id`</span> <span class="built_in">INT</span>,</span><br><span class="line">	<span class="keyword">INDEX</span> <span class="string">`index_id`</span> (<span class="string">`id`</span>) <span class="keyword">USING</span> BTREE</span><br><span class="line">)<span class="keyword">ENGINE</span>=<span class="keyword">INNODB</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=UTF8;</span><br></pre></td></tr></table></figure></div>



<p>表创建后增加：</p>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="SQL"><figure class="iseeu highlight /sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 语法：CREATE INDEX 索引名 ON 表名(字段名)</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">INDEX</span> index_name <span class="keyword">ON</span> a(<span class="keyword">name</span>);</span><br></pre></td></tr></table></figure></div>



<h4 id="4、全文索引（Full-Text）"><a href="#4、全文索引（Full-Text）" class="headerlink" title="4、全文索引（Full Text）"></a>4、全文索引（Full Text）</h4><p>当在MySQL中存储了文本，比如某个字段的值为<code>坚决贯彻党的十八大精神</code>，我们想用贯彻和十八大作为关键字时都可以搜索到这条记录。那么只能使用<code>like</code>关键字。而对于<code>like</code>我们知道，当不是用左边前缀搜索的时候，无法命中索引，因此对于这条语句 <code>select * from article where content like &#39;%贯彻%&#39;</code>，MySQL只能进行全表扫描，逐一进行匹配，这样的效率极其低下。</p>
<p>而全文索引的数据结构为 Full Text，通过建立倒排索引，可以极大的提升检索效率。</p>
<p>目前，MySQL仅可以在char、varchar、text属性的列上建立全文索引。</p>
<p>除了数据结构不同，创建全文索引的时机与创建其他类型的索引没什么不同：</p>
<p>表创建时设置：</p>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="SQL"><figure class="iseeu highlight /sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 语法：FULLTEXT INDEX 索引名 (字段名)</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> b(</span><br><span class="line">	<span class="string">`name`</span> <span class="built_in">VARCHAR</span>(<span class="number">100</span>),</span><br><span class="line">	FULLTEXT <span class="keyword">INDEX</span> <span class="string">`index_name`</span> (<span class="string">`name`</span>)</span><br><span class="line">)<span class="keyword">ENGINE</span>=<span class="keyword">INNODB</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=UTF8;</span><br></pre></td></tr></table></figure></div>



<p>表创建后增加：</p>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="SQL"><figure class="iseeu highlight /sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 语法：CREATE FULLTEXT INDEX 索引名 ON 表名(字段名)</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">CREATE</span> FULLTEXT <span class="keyword">INDEX</span> full_name <span class="keyword">ON</span> a(<span class="keyword">name</span>);</span><br></pre></td></tr></table></figure></div>



<p>MySQL的全文索引查询有多种模式，常用的两种为<strong>自然语言模式</strong>和 Boolean 模式：</p>
<p>自然语言模式：</p>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="SQL"><figure class="iseeu highlight /sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 语法：SELECT 显示列 FROM 表名 WHERE MATCH (搜索序列) AGAINST('搜索内容' IN NATURAL LANGUAGE MODE)</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> article <span class="keyword">WHERE</span> <span class="keyword">MATCH</span> (title,<span class="keyword">content</span>) AGAINST(<span class="string">'贯彻'</span> <span class="keyword">IN</span> <span class="keyword">NATURAL</span> <span class="keyword">LANGUAGE</span> <span class="keyword">MODE</span>);</span><br></pre></td></tr></table></figure></div>



<p>Boolean 模式通过一些操作符,来指定搜索词在结果中的包含情况：</p>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="SQL"><figure class="iseeu highlight /sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 语法：SELECT 显示列 FROM 表名 WHERE MATCH (搜索序列) AGAINST('+贯彻 -精神' IN BOOLEAN MODE)</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> article <span class="keyword">WHERE</span> <span class="keyword">MATCH</span> (title,<span class="keyword">content</span>) AGAINST(<span class="string">'搜索表达式'</span> <span class="keyword">IN</span> <span class="built_in">BOOLEAN</span> <span class="keyword">MODE</span>);</span><br></pre></td></tr></table></figure></div>



<p><strong>注意</strong>：在 MySQL 5.6 版本以前，只有 MyISAM 存储引擎支持全文引擎。在5.6版本中，InnoDB加入了对全文索引的支持，但是不支持中文全文索引。在5.7.6版本，MySQL内置了ngram全文解析器，用来支持亚洲语种的分词。</p>
<h4 id="5、索引操作"><a href="#5、索引操作" class="headerlink" title="5、索引操作"></a>5、索引操作</h4><p> 5.1、查看索引：</p>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="SQL"><figure class="iseeu highlight /sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 语法：SHOW INDEX FROM 表名</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">SHOW</span> <span class="keyword">INDEX</span> <span class="keyword">FROM</span> a;</span><br></pre></td></tr></table></figure></div>



<p> 5.2、删除索引</p>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="SQL"><figure class="iseeu highlight /sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 语法：ALTER TABLE 表名 DROP INDEX 索引名</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> a <span class="keyword">DROP</span> <span class="keyword">INDEX</span> index_name;</span><br></pre></td></tr></table></figure></div>



<h3 id="五、联合索引"><a href="#五、联合索引" class="headerlink" title="五、联合索引"></a>五、联合索引</h3><p>相比单索引，联合索引的应用场景更广。联合索引又叫复合索引。联合索引中的字段遵循从左到右的使用原则，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是 index (a,b,c)， 可以支持 （a）| （a,b）| （a,b,c）  3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。</p>
<p>比如有一条语句：</p>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="SQL"><figure class="iseeu highlight /sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> <span class="keyword">user</span> <span class="keyword">where</span> adress=<span class="string">'beijing'</span> <span class="keyword">and</span> <span class="keyword">name</span>=<span class="string">'张三'</span>;</span><br></pre></td></tr></table></figure></div>

<p>如果我们是在表上分别创建单个索引 adress  name 的话，由于 SQL 查询每次只能使用<strong>一个索引</strong>，所以虽然这样已经相对不做索引时全表扫描提升了很高效率。</p>
<p>但是如果在 adress、name 两列上创建联合索引的话将带来更高的效率。如果我们创建了 ( id, adress,name) 的联合索引，那么其实相当于创建了 (id,adress,name)、(id,adress)、(id) 三个索引，这被称为最佳左前缀特性。</p>
<p>因此我们在创建联合索引时应该将<strong>最常用</strong>作限制条件的列放在<strong>最左边</strong>，依次递减。</p>
<p>创建联合索引：</p>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="SQL"><figure class="iseeu highlight /sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 语法：CREATE INDEX 索引名 ON 表名(字段序列)</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">INDEX</span> index_id_name <span class="keyword">ON</span> a(<span class="keyword">id</span>,<span class="keyword">name</span>);</span><br></pre></td></tr></table></figure></div>





<h3 id="六、执行分析"><a href="#六、执行分析" class="headerlink" title="六、执行分析"></a>六、执行分析</h3><p>explain 命令可以用来分析 SQL 语句执行情况，比较使用索引和未使用的执行效率。</p>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="SQL"><figure class="iseeu highlight /sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 语法：EXPLAIN 执行语句</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">EXPLAIN</span> <span class="keyword">SELECT</span> * <span class="keyword">FROM</span> a;</span><br></pre></td></tr></table></figure></div>



<h3 id="七、建立索引优化时需要注意的问题"><a href="#七、建立索引优化时需要注意的问题" class="headerlink" title="七、建立索引优化时需要注意的问题"></a>七、建立索引优化时需要注意的问题</h3><p>设计好MySql的索引可以让你的数据库飞起来，大大的提高数据库效率。设计MySql索引的时候有一下几点注意：</p>
<h4 id="1，索引不要过度使用"><a href="#1，索引不要过度使用" class="headerlink" title="1，索引不要过度使用"></a>1，索引不要过度使用</h4><p>​    如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，</p>
<h4 id="2．什么情况下使用索引"><a href="#2．什么情况下使用索引" class="headerlink" title="2．什么情况下使用索引"></a>2．什么情况下使用索引</h4><ul>
<li><p>表的主键、外键必须有索引</p>
</li>
<li><p>数据量超过 300 的表应该有索引</p>
</li>
<li><p>经常与其他表进行连接的表，在连接字段上应该建立索引</p>
</li>
<li><p>经常出现在Where子句中的字段，特别是大表的字段，应该建立索引</p>
</li>
<li><p>索引应该建在选择性高的字段上</p>
</li>
<li><p>索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引</p>
</li>
<li><p>查询中排序的字段：排序的字段如果通过索引去访问那将大大提高排序速度</p>
</li>
<li><p>查询中统计或分组统计的字段</p>
</li>
<li></li>
</ul>
<h4 id="3、什么情况下应不建或少建索引"><a href="#3、什么情况下应不建或少建索引" class="headerlink" title="3、什么情况下应不建或少建索引"></a>3、什么情况下应不建或少建索引</h4><ul>
<li><p>表记录太少</p>
</li>
<li><p>频繁进行数据操作的表，不要建立太多的索引</p>
</li>
<li><p>数据重复且分布平均的表字段：如一个表有10万行记录，有一个字段 a 只有 true 和 false 两种值，且每个值的分布概率大约为50%，那么对这种表 a 字段建索引一般不会提高数据库的查询速度。</p>
</li>
</ul>
<p>​        索引有优点也有缺点，索引中的字段被更新的时候，不仅仅要更新表中的数据，还要更新索引数据，以确保索引信息是准确的，这使得 IO 访问量较大增加，加大了整个存储的负载.<br>​        当然,，不是存在更新的字段就适合创建索引，如果通过这个字段的更新并不是很多，可能较长时间才会执行一次更新，查询反而比更新更频繁，那这样的字段还是适合创建索引得。更新所带来的额外性能开销也是可以接受的。</p>
<h4 id="4、索引失效问题"><a href="#4、索引失效问题" class="headerlink" title="4、索引失效问题"></a>4、索引失效问题</h4><ul>
<li>索引不会包含有NULL值的列</li>
<li>SQL 查询只使用一个索引：<ul>
<li>只设置了单索引得话，多个字段查询只会使用最左索引，where 子句和 order by 子句不能同时使用索引，可根据需求创建复合索引。</li>
</ul>
</li>
<li>like 语句如何使用索引也是一个问题</li>
<li>不要在列上进行运算</li>
<li>NOT IN 不会使用索引，NOT IN 可用 NOT EXISTS代替。</li>
</ul>
<h3 id="八、总结"><a href="#八、总结" class="headerlink" title="八、总结"></a>八、总结</h3><p>数据库利用各种各样的快速查找、定位技术，能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引查询速度能加快成千上万倍。</p>
<p>但索引也不应该滥用，不当使用反而会加大性能开销。应根据业务实际需求酌情使用。</p>
<p><strong>注意：以下内容大多基于 MySql 测试，其他数据库也差不多</strong>。</p>
<hr>
<blockquote>
<p>2021年11月15日 稿</p>
</blockquote>

      
    </div>
    
      <footer class="article-footer">
        完
      </footer>
    
  </div>
  
    
<nav id="article-nav">
  <div class="article-nav-block">
    
      <a href="/2021/11/16/046%E3%80%81%E6%95%B0%E6%8D%AE%E5%BA%93%E4%BA%8B%E5%8A%A1%E7%9B%B8%E5%85%B3%E7%9F%A5%E8%AF%86%E6%A2%B3%E7%90%86/" id="article-nav-newer" class="article-nav-link-wrap">
        <strong class="article-nav-caption"></strong>
        <div class="article-nav-title">
          
            046、数据库事务相关知识梳理
          
        </div>
      </a>
    
  </div>
  <div class="article-nav-block">
    
      <a href="/2021/11/12/044.%E6%95%B0%E6%8D%AE%E5%BA%93%E5%A4%9A%E8%A1%A8%E6%9F%A5%E8%AF%A2%E6%96%B9%E5%BC%8F/" id="article-nav-older" class="article-nav-link-wrap">
        <div class="article-nav-title">044.数据库多表查询方式</div>
        <strong class="article-nav-caption"></strong>
      </a>
    
  </div>
</nav>

    <link rel="stylesheet" href="/css/gitment.css"> 
<script src="/js/gitment.js"></script>

<div id="gitmentContainer"></div>

<script>
var gitment = new Gitment({
  owner: '',
  repo: '',
  oauth: {
    client_id: '',
    client_secret: '',
  },
})
gitment.render('gitmentContainer')
</script>

  
  
</article>
</section>
        <aside id="sidebar">
  
    <div class="widget-box">
  <div class="avatar-box">
    <img class="avatar" src="/images/avatar.png" title="图片来自网络"></img>
    <h3 class="avatar-name">
      
        似南
      
    </h3>
    <p class="avatar-slogan">
      天空透露着微光，照亮虚无迷惘。
    </p>
  </div>
</div>


  
    
  <div class="widget-box">
    <h3 class="widget-title">分类</h3>
    <div class="widget">
      <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/categories/Web%E5%85%A8%E6%A0%88%E6%9E%B6%E6%9E%84/">Web全栈架构</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/categories/Web%E5%85%A8%E6%A0%88%E6%9E%B6%E6%9E%84/Java/">Java</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Web%E5%85%A8%E6%A0%88%E6%9E%B6%E6%9E%84/JavaScript/">JavaScript</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Web%E5%85%A8%E6%A0%88%E6%9E%B6%E6%9E%84/PHP/">PHP</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/MySQL/">MySQL</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/categories/%E7%BB%BC%E5%90%88%E6%8A%80%E6%9C%AF%E6%A0%88/">综合技术栈</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/categories/%E7%BB%BC%E5%90%88%E6%8A%80%E6%9C%AF%E6%A0%88/Git/">Git</a></li></ul></li><li class="category-list-item"><a class="category-list-link" href="/categories/%E8%BF%90%E7%BB%B4%E6%8A%80%E6%9C%AF/">运维技术</a><ul class="category-list-child"><li class="category-list-item"><a class="category-list-link" href="/categories/%E8%BF%90%E7%BB%B4%E6%8A%80%E6%9C%AF/Linux%E7%B3%BB%E7%BB%9F%E8%BF%90%E7%BB%B4/">Linux系统运维</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/%E8%BF%90%E7%BB%B4%E6%8A%80%E6%9C%AF/Web%E5%AE%89%E5%85%A8/">Web安全</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/%E8%BF%90%E7%BB%B4%E6%8A%80%E6%9C%AF/%E6%95%B0%E9%80%9A/">数通</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/%E8%BF%90%E7%BB%B4%E6%8A%80%E6%9C%AF/%E8%99%9A%E6%8B%9F%E5%8C%96/">虚拟化</a></li></ul></li></ul>
    </div>
  </div>


  
    
  <div class="widget-box">
    <h3 class="widget-title">标签云</h3>
    <div class="widget tagcloud">
      <a href="/tags/Docker/" style="font-size: 10px;">Docker</a> <a href="/tags/Hexo/" style="font-size: 10px;">Hexo</a> <a href="/tags/JDBC/" style="font-size: 15px;">JDBC</a> <a href="/tags/JavaGUI/" style="font-size: 10px;">JavaGUI</a> <a href="/tags/MyBatis/" style="font-size: 10px;">MyBatis</a> <a href="/tags/SSH/" style="font-size: 10px;">SSH</a> <a href="/tags/Servlet/" style="font-size: 10px;">Servlet</a> <a href="/tags/Socket/" style="font-size: 10px;">Socket</a> <a href="/tags/Spring/" style="font-size: 10px;">Spring</a> <a href="/tags/SpringBoot/" style="font-size: 15px;">SpringBoot</a> <a href="/tags/SpringCloud/" style="font-size: 10px;">SpringCloud</a> <a href="/tags/Telnet/" style="font-size: 10px;">Telnet</a> <a href="/tags/VLAN/" style="font-size: 15px;">VLAN</a> <a href="/tags/%E4%BC%98%E5%8C%96/" style="font-size: 10px;">优化</a> <a href="/tags/%E4%BD%BF%E7%94%A8%E6%8C%87%E5%8D%97/" style="font-size: 10px;">使用指南</a> <a href="/tags/%E5%8D%8F%E8%AE%AE%E5%88%86%E6%9E%90/" style="font-size: 10px;">协议分析</a> <a href="/tags/%E5%A2%9E%E5%88%A0%E6%94%B9%E6%9F%A5/" style="font-size: 10px;">增删改查</a> <a href="/tags/%E5%A4%87%E4%BB%BD/" style="font-size: 10px;">备份</a> <a href="/tags/%E5%A4%9A%E7%BA%BF%E7%A8%8B/" style="font-size: 10px;">多线程</a> <a href="/tags/%E5%A4%9A%E8%A1%A8%E6%9F%A5%E8%AF%A2/" style="font-size: 10px;">多表查询</a> <a href="/tags/%E5%AD%A6%E4%B9%A0%E6%8C%87%E5%8D%97/" style="font-size: 20px;">学习指南</a> <a href="/tags/%E6%80%9D%E7%A7%91%E6%95%B0%E9%80%9A/" style="font-size: 15px;">思科数通</a> <a href="/tags/%E6%8E%A5%E5%8F%A3%E8%AF%B7%E6%B1%82/" style="font-size: 10px;">接口请求</a> <a href="/tags/%E6%95%85%E9%9A%9C%E6%8E%92%E9%99%A4/" style="font-size: 10px;">故障排除</a> <a href="/tags/%E6%97%A0%E7%BA%BFWiFi/" style="font-size: 10px;">无线WiFi</a> <a href="/tags/%E6%9D%83%E9%99%90%E6%8E%A7%E5%88%B6/" style="font-size: 10px;">权限控制</a> <a href="/tags/%E7%9B%91%E6%8E%A7/" style="font-size: 10px;">监控</a> <a href="/tags/%E7%AB%AF%E5%8F%A3%E9%85%8D%E7%BD%AE/" style="font-size: 10px;">端口配置</a> <a href="/tags/%E7%B4%A2%E5%BC%95/" style="font-size: 15px;">索引</a> <a href="/tags/%E8%99%9A%E6%8B%9F%E6%9C%BA/" style="font-size: 10px;">虚拟机</a> <a href="/tags/%E8%AF%BB%E4%B9%A6%E7%AC%94%E8%AE%B0/" style="font-size: 10px;">读书笔记</a> <a href="/tags/%E8%B7%AF%E7%94%B1%E9%85%8D%E7%BD%AE/" style="font-size: 10px;">路由配置</a> <a href="/tags/%E9%9B%86%E7%BE%A4/" style="font-size: 10px;">集群</a>
    </div>
  </div>

  
    
  <div class="widget-box">
    <h3 class="widget-title">归档</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/archives/2021/11/">十一月 2021</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2021/07/">七月 2021</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/12/">十二月 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/11/">十一月 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/10/">十月 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/08/">八月 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/06/">六月 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/05/">五月 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/04/">四月 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/03/">三月 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/01/">一月 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/12/">十二月 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/11/">十一月 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/10/">十月 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/08/">八月 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/07/">七月 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/06/">六月 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/05/">五月 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/04/">四月 2019</a></li></ul>
    </div>
  </div>

  
    
  <div class="widget-box">
    <h3 class="widget-title">最新文章</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/2021/11/16/046%E3%80%81%E6%95%B0%E6%8D%AE%E5%BA%93%E4%BA%8B%E5%8A%A1%E7%9B%B8%E5%85%B3%E7%9F%A5%E8%AF%86%E6%A2%B3%E7%90%86/">046、数据库事务相关知识梳理</a>
          </li>
        
          <li>
            <a href="/2021/11/15/045.%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B4%A2%E5%BC%95%E7%9F%A5%E8%AF%86%E6%A2%B3%E7%90%86/">045.数据库索引知识梳理</a>
          </li>
        
          <li>
            <a href="/2021/11/12/044.%E6%95%B0%E6%8D%AE%E5%BA%93%E5%A4%9A%E8%A1%A8%E6%9F%A5%E8%AF%A2%E6%96%B9%E5%BC%8F/">044.数据库多表查询方式</a>
          </li>
        
          <li>
            <a href="/2021/11/03/043.Java%20%E5%A4%9A%E7%BA%BF%E7%A8%8B%E6%A6%82%E8%A6%81/">043.Java 多线程概要</a>
          </li>
        
          <li>
            <a href="/2021/07/31/042.SpringCloud%E5%BE%AE%E6%9C%8D%E5%8A%A1%E5%AE%9E%E7%8E%B0/">042.SpringCloud 微服务实现</a>
          </li>
        
      </ul>
    </div>
  </div>

  
      <div class="widget-box">
    <h3 class="widget-title">友链</h3>
    <div class="widget">
      
        <a style="display: block;" href="https://yiluyanxia.github.io/" title target='_blank'
        >一路眼瞎</a>
      
    </div>
  </div>

  
</aside>
      </div>
      <footer id="footer">
  <div class="foot-box global-width">
    &copy; 2022 FaithSinan &nbsp;&nbsp;
    驱动于 <a href="http://hexo.io/" target="_blank">Hexo</a>
    &nbsp;|&nbsp;基于 <a href="https://github.com/yiluyanxia/hexo-theme-antiquity" target="_blank" rel="noopener">antiquity</a>
    <br>
    <script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
    <span id="busuanzi_container_site_pv">不蒜子告之   阁下是第<span id="busuanzi_value_site_pv"></span>个访客</span>
  </div>
</footer>
      <script src="https://code.jquery.com/jquery-2.0.3.min.js"></script>
<script>
if (!window.jQuery) {
var script = document.createElement('script');
script.src = "/js/jquery-2.0.3.min.js";
document.body.write(script);
}
</script>

  
<link rel="stylesheet" href="/fancybox/jquery.fancybox.css">

  
<script src="/fancybox/jquery.fancybox.pack.js"></script>




<script src="/js/script.js"></script>




    </div>
    <nav id="mobile-nav" class="mobile-nav-box">
  <div class="mobile-nav-img mobile-nav-top"></div>
  
    <a href="/" class="mobile-nav-link">首页</a>
  
    <a href="/archives" class="mobile-nav-link">归档</a>
  
  <div class="mobile-nav-img  mobile-nav-bottom"></div>
</nav>    
  </div>
</body>
</html>